﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Globalization;
using System.IO;

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

using SpreadsheetLight;
using SpreadsheetLight.Drawing;

using BusinessManagementStudio.Models.BusinessObject.Entities;
using BusinessManagementStudio.Utilities.Excel_Templates;

namespace BusinessManagementStudio.Utilities
{
    public static class ExcelOpenXmlUtil
    {
        public static List<Order> GetAllJLLOrderRecordsFromFile(String filePath, String TabName, int StartRow)
        {
            List<Order> RecordsFromFile = new List<Order>();

            using (SpreadsheetDocument document = SpreadsheetDocument.Open(filePath, false))
            {
                try
                {
                    //Access the main Workbook part, which contains all references.
                    WorkbookPart workbookPart = document.WorkbookPart;
                    //Get the first worksheet. 
                    WorksheetPart worksheetPart = GetWorksheetPartByName(document, TabName);
                    // The SheetData object will contain all the data.
                    SheetData sheetData = worksheetPart.Worksheet.Elements<SheetData>().First();
                    SharedStringTable strTable = workbookPart.SharedStringTablePart.SharedStringTable;

                    int currRow = 1;

                    foreach (Row r in sheetData.Elements<Row>())
                    {
                        // Exclude the data from the first two rows
                        if (currRow >= StartRow)
                        {
                            if (GetCellText(r.Elements<Cell>().ElementAt(0), strTable) == null)
                                break;

                            //string SONo = GetCellText(r.Elements<Cell>().ElementAt(0), strTable);
                            //int Month = int.Parse(SONo.Substring(0, 2));
                            //int Day = int.Parse(SONo.Substring(2, 2));

                            //if (Month == DateTime.Today.Month && Day == DateTime.Today.Day)
                            //{
                                Order record = GetJLLOrderExcelText(GetCellText(r.Elements<Cell>().ElementAt(0), strTable)
                                    , GetCellText(r.Elements<Cell>().ElementAt(1), strTable), GetCellText(r.Elements<Cell>().ElementAt(2), strTable)
                                    , GetCellText(r.Elements<Cell>().ElementAt(3), strTable), GetCellText(r.Elements<Cell>().ElementAt(4), strTable));

                                RecordsFromFile.Add(record);
                            //}
                        }

                        currRow++;
                    }
                }
                catch (Exception e)
                {
                }
            }

            return RecordsFromFile;
        }

        private static WorksheetPart GetWorksheetPartByName(SpreadsheetDocument document, string sheetName)
        {
            IEnumerable<Sheet> sheets = document.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>().Where(s => s.Name == sheetName);

            if (sheets.Count() == 0)
            {
                // The specified worksheet does not exist.
                return null;
            }

            string relationshipId = sheets.First().Id.Value;
            WorksheetPart worksheetPart = (WorksheetPart)document.WorkbookPart.GetPartById(relationshipId);
            return worksheetPart;
        }

        private static string GetCellText(Cell cell, SharedStringTable strTable)
        {
            if (cell.ChildElements.Count == 0)
            {
                return null;
            }
            String val = cell.CellValue.InnerText;
            if (cell.DataType != null && cell.DataType == CellValues.SharedString)
            {
                val = strTable.ChildElements[int.Parse(val)].InnerText;
            }
            return val;
        }

        public static Order GetJLLOrderExcelText(String SONo, String Description, String Location, String WONo, String Emergency)
        {
            Order jllOrder = new Order();

            jllOrder.ServiceOrderNo = SONo;
            jllOrder.Description = Description;
            jllOrder.Location = Location;
            jllOrder.WorkOrderNo = WONo;
            jllOrder.Emergency = Emergency;
            jllOrder.CompanyType = "7";
            jllOrder.CompanyName = "JLL - (ORDER)";

            return jllOrder;
        }

        public static bool CreateQuotationOrInvoiceInExcelFormat(String tabName, ExcelMain data)
        {
            bool IsSuccess = false;

            try
            {
                String srcPath = data.ExcelSrcPathForGeneralTemplate;

                if (data.Data.IsJLLPOInvoce == true || data.Data.IsJLLPOQuotation == true)
                    srcPath = data.ExcelSrcPathForJLLPOTemplate;

                String newTabName = data.Data.QuotationNo;
                String tempDestPath = data.ExcelDestPath + data.ExcelFileName;

                if (data.Data.IsQuotation == false && data.Data.IsInvoice == true)
                    newTabName = data.Data.InvoiceNo;

                // (1) Rename new tab name
                if (data.Data.IsQuotation == true && data.Data.IsInvoice == false)
                {
                    using (SLDocument doc = new SLDocument(srcPath, tabName))
                    {
                        doc.RenameWorksheet(tabName, newTabName);
                        doc.SaveAs(tempDestPath);
                    }
                }
                else if (data.Data.IsQuotation == false && data.Data.IsInvoice == true)
                {
                    if (File.Exists(tempDestPath) == true)
                    {
                        using (SLDocument doc = new SLDocument(tempDestPath, tabName))
                        {
                            doc.RenameWorksheet(tabName, newTabName);
                            doc.Save();
                        }
                    }
                    else
                        return false;
                }

                // (2) Plug In Data into Excel
                if (File.Exists(tempDestPath) == true)
                {
                    using (SLDocument doc = new SLDocument(tempDestPath, newTabName))
                    {
                        int NoOfItems = (data.Data.IsJLLOrderQuotation == true || data.Data.IsJLLOrderInvoice == true) ? data.Data.QuotationList.Count : data.Data.QuotationList.Count;
                        int NoOfPages = (int)Math.Ceiling((double)NoOfItems / (double)data.MaxNoOfRecords);
                        int CurrentItemColumnNo = 16;
                        int NoOfRowsToBeAdded = (NoOfItems) + (16 * (NoOfPages - 1)) + (2 * NoOfPages);
                        doc.InsertRow(CurrentItemColumnNo + 2, NoOfRowsToBeAdded);
                        Decimal TotalAmount = 0;
                        decimal CarryForward = 0;
                        decimal BroughtForward = 0;

                        for (int j = 0; j < NoOfPages; j++)
                        {
                            int CurrentPage = j + 1;
                            int startAnchor = ((CurrentPage - 1) * 38) + 2;
                            
                            int EndOfPageRow = CurrentPage * 40;
                            int EndOfPageColumn = 12;

                            if (CurrentPage >= 2 && CurrentPage <= NoOfPages)
                            {
                                int tempEndRow = (j * 40);
                                doc.CopyRow(1, CurrentItemColumnNo, startAnchor, false);
                                SLPicture pic = new SLPicture(data.CompanyLogoPath);
                                pic.ResizeInPixels(150, 56);
                                pic.SetPosition((double)tempEndRow, 7.8);
                                doc.InsertPicture(pic);
                            }

                            #region Billing Info
                            int tempRowNumber = (j * 39) + 4;
                            doc.SetCellValue("C" + tempRowNumber, data.Data.client.BillingName);
                            tempRowNumber = (j * 39) + 5;
                            doc.SetCellValue("C" + tempRowNumber, data.Data.client.BillingAddress1);
                            tempRowNumber = (j * 39) + 6;
                            doc.SetCellValue("C" + tempRowNumber, data.Data.client.BillingAddress2);
                            tempRowNumber = (j * 39) + 7;
                            doc.SetCellValue("C" + tempRowNumber, data.Data.client.BillingAddress3);
                            tempRowNumber = (j * 39) + 9;
                            doc.SetCellValue("C" + tempRowNumber, data.Data.client.AttentionTo);

                            String tempQuotationNo = data.Data.QuotationNo;

                            if (data.Data.IsJLLPOQuotation == true || data.Data.IsJLLPOInvoce == true)
                            {
                                String[] split = data.Data.QuotationNo.Split('-');
                                tempQuotationNo = split[0] + "-" + split[1] + "/" + split[2] + "/" + split[3];
                            }
                            String PageStrn = CurrentPage + " of " + NoOfPages;

                            if (data.Data.IsQuotation == true && data.Data.IsInvoice == false)
                            {
                                if (data.Data.IsJLLOrderQuotation == true)
                                {
                                    tempRowNumber = (j * 39) + 4;
                                    doc.SetCellValue("J" + tempRowNumber, data.Data.QuotationNo);
                                    tempRowNumber = (j * 39) + 5;
                                    doc.SetCellValue("J" + tempRowNumber, data.Data.WorkOrderNo);
                                    tempRowNumber = (j * 39) + 6;
                                    doc.SetCellValue("J" + tempRowNumber, data.Data.QuotationDate);
                                    tempRowNumber = (j * 39) + 7;
                                    doc.SetCellValue("J" + tempRowNumber, PageStrn);
                                    tempRowNumber = (j * 39) + 8;
                                    doc.SetCellValue("J" + tempRowNumber, data.Data.ReferenceNo);
                                    tempRowNumber = (j * 39) + 9;
                                    doc.SetCellValue("J" + tempRowNumber, data.Data.VendorNo);
                                }
                                else if (data.Data.IsJLLPOQuotation == true)
                                {
                                    tempRowNumber = (j * 39) + 4;
                                    doc.SetCellValue("J" + tempRowNumber, tempQuotationNo);
                                    tempRowNumber = (j * 39) + 5;
                                    doc.SetCellValue("J" + tempRowNumber, data.Data.QuotationDate);
                                    tempRowNumber = (j * 39) + 6;
                                    doc.SetCellValue("J" + tempRowNumber, PageStrn);
                                    tempRowNumber = (j * 39) + 7;
                                    doc.SetCellValue("J" + tempRowNumber, data.Data.ReferenceNo);
                                    tempRowNumber = (j * 39) + 8;
                                    doc.SetCellValue("J" + tempRowNumber, data.Data.VendorNo);
                                }
                            }
                            else if (data.Data.IsQuotation == false && data.Data.IsInvoice == true)
                            {
                                if (data.Data.IsJLLOrderInvoice == true)
                                {
                                    tempRowNumber = (j * 39) + 4;
                                    doc.SetCellValue("J" + tempRowNumber, data.Data.InvoiceNo);
                                    tempRowNumber = (j * 39) + 5;
                                    doc.SetCellValue("J" + tempRowNumber, data.Data.QuotationNo);
                                    tempRowNumber = (j * 39) + 6;
                                    doc.SetCellValue("J" + tempRowNumber, data.Data.WorkOrderNo);
                                    tempRowNumber = (j * 39) + 7;
                                    doc.SetCellValue("J" + tempRowNumber, data.Data.InvoiceDate);
                                    tempRowNumber = (j * 39) + 8;
                                    doc.SetCellValue("J" + tempRowNumber, PageStrn);
                                    tempRowNumber = (j * 39) + 9;
                                    doc.SetCellValue("J" + tempRowNumber, data.Data.ReferenceNo);
                                    tempRowNumber = (j * 39) + 10;
                                    doc.SetCellValue("J" + tempRowNumber, data.Data.VendorNo);
                                }
                                else if (data.Data.IsJLLPOInvoce == true)
                                {
                                    tempRowNumber = (j * 39) + 4;
                                    doc.SetCellValue("J" + tempRowNumber, data.Data.InvoiceNo);
                                    tempRowNumber = (j * 39) + 5;
                                    doc.SetCellValue("J" + tempRowNumber, data.Data.InvoiceDate);
                                    tempRowNumber = (j * 39) + 6;
                                    doc.SetCellValue("J" + tempRowNumber, data.Data.PONo);
                                    tempRowNumber = (j * 39) + 7;
                                    doc.SetCellValue("J" + tempRowNumber, data.Data.WorkOrderNo);
                                    tempRowNumber = (j * 39) + 8;
                                    doc.SetCellValue("J" + tempRowNumber, PageStrn);
                                    tempRowNumber = (j * 39) + 9;
                                    doc.SetCellValue("J" + tempRowNumber, data.Data.ReferenceNo);
                                    tempRowNumber = (j * 39) + 10;
                                    doc.SetCellValue("J" + tempRowNumber, data.Data.VendorNo);
                                }
                            }
                            #endregion

                            #region Service Address
                            int tempSANo = 13;

                            if (data.Data.IsJLLOrderQuotation == true || data.Data.IsJLLPOQuotation == true)
                                tempSANo = 12;

                            tempRowNumber = (j * 39) + tempSANo;

                            String tStrn = data.Data.Location + " - " + data.Data.ServiceDetail;

                            if(data.Data.ServiceDetail == "" || data.Data.ServiceDetail == null)
                                tStrn = data.Data.Location;

                            doc.SetCellValue("D" + tempRowNumber, tStrn);
                            #endregion

                            #region Items List & Work Completion Date
                            String WorkCompletionDateCellRange = "A20";
                            int WorkCompletionDateColumnNo = 20;
                            int TotalAmountColumnNo = 19;
                            int InvoiceWordColumnNo = 23;

                            if (data.Data.IsJLLOrderQuotation == true || data.Data.IsJLLPOQuotation == true)
                            {
                                WorkCompletionDateCellRange = "A19";
                                WorkCompletionDateColumnNo = 19;
                                TotalAmountColumnNo = 18;
                                InvoiceWordColumnNo = 22;
                            }

                            // (b) Find out the number of rows to be added
                            #region Rows Calculation
                            tempRowNumber = (j * 38) + CurrentItemColumnNo;
                            SLStyle style = doc.GetCellStyle("J" + tempRowNumber);
                            style.FormatCode = "#,##0.00";
                            doc.SetCellStyle("J" + tempRowNumber, style);

                            WorkCompletionDateColumnNo += NoOfRowsToBeAdded;
                            InvoiceWordColumnNo += NoOfRowsToBeAdded;
                            WorkCompletionDateCellRange = "A" + WorkCompletionDateColumnNo;
                                

                            int tempEnd = (CurrentPage * CurrentItemColumnNo) + 21;
                            if (CurrentPage == NoOfPages)
                            {
                                if (data.Data.IsJLLOrderQuotation == true || data.Data.IsJLLPOQuotation == true)
                                    tempEnd = (j * 40) + CurrentItemColumnNo + (NoOfItems % data.MaxNoOfRecords);
                                else
                                    tempEnd = (j * 40) + CurrentItemColumnNo + (NoOfItems % data.MaxNoOfRecords) + 1;
                            }

                            if (CurrentPage == 1)
                            {
                                if (data.Data.IsJLLOrderQuotation == true || data.Data.IsJLLPOQuotation == true)
                                    tempRowNumber = CurrentItemColumnNo + 1;
                                else
                                    tempRowNumber = CurrentItemColumnNo + 2;
                            }
                            else
                            {
                                if (data.Data.IsJLLOrderQuotation == true || data.Data.IsJLLPOQuotation == true)
                                    tempRowNumber = (j * 40) + (CurrentItemColumnNo - 1);
                                else
                                    tempRowNumber = (j * 40) + CurrentItemColumnNo;
                            }

                            //if (data.Data.IsJLLOrderQuotation == true || data.Data.IsJLLPOQuotation == true)
                            //{
                            //    tempEnd = (CurrentPage * CurrentItemColumnNo) + 20;
                            //    if (CurrentPage == NoOfPages)
                            //        tempEnd = (j * 40) + (CurrentItemColumnNo - 1) + (NoOfItems % data.MaxNoOfRecords);

                            //    if (CurrentPage == 1)
                            //        tempRowNumber = CurrentItemColumnNo + 1;
                            //    else
                            //        tempRowNumber = (j * 40) + CurrentItemColumnNo;
                            //}

                            for (int i = tempRowNumber; i < tempEnd; i++)
                            {
                                int tempRowNo = CurrentItemColumnNo + 1;
                                style = doc.GetCellStyle("A" + tempRowNo);
                                doc.SetCellStyle("A" + i, style);
                                style = doc.GetCellStyle("B" + tempRowNo);
                                doc.SetCellStyle("B" + i, style);
                                doc.MergeWorksheetCells("B" + i, "E" + i);
                                style = doc.GetCellStyle("F" + tempRowNo);
                                doc.SetCellStyle("F" + i, style);
                                style = doc.GetCellStyle("H" + tempRowNo);
                                doc.SetCellStyle("H" + i, style);
                                style = doc.GetCellStyle("I" + tempRowNo);
                                doc.SetCellStyle("I" + i, style);
                                style = doc.GetCellStyle("J" + tempRowNo);
                                style.FormatCode = "#,##0.00";
                                doc.SetCellStyle("J" + i, style);
                                style = doc.GetCellStyle("K" + tempRowNo);
                                style.FormatCode = "#,##0.00";
                                doc.SetCellStyle("K" + i, style);
                            }
                            #endregion

                            String tempStrn = "";
                            int SIndex = GetStartIndex(data, j); ;
                            int EIndex = GetEndIndex(data, CurrentPage, NoOfPages);
                            int tNo = tempRowNumber;
                            if (CurrentPage == 1)
                                tNo = tNo - 1;

                            #region Items Update
                            if (CurrentPage >= 2 && CurrentPage <= NoOfPages)
                            {
                                doc.MergeWorksheetCells("I" + tNo, "J" + tNo);
                                doc.SetCellValue("I" + tNo, "Brought Forward:");
                                style = doc.GetCellStyle("K16");
                                style.FormatCode = "#,##0.00";
                                style.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
                                style.Border.BottomBorder.Color = System.Drawing.Color.Black;
                                doc.SetCellStyle("K" + tNo, style);
                                doc.SetCellValue("K" + tNo, BroughtForward);
                                tNo += 1;
                            }

                            int tempIndex = 0;

                            if (data.Data.IsJLLOrderQuotation == true || data.Data.IsJLLOrderInvoice == true)
                            {
                                #region JLLOrder Items
                                for (int i = SIndex, m = tNo; i < EIndex && m < tempEnd; i++, m++)
                                {
                                    tempStrn = (data.Data.QuotationList[i].ItemNo == 0) ? "" : data.Data.QuotationList[i].ItemNo.ToString("00") + ".";
                                    doc.SetCellValue("A" + m, tempStrn);
                                    doc.SetCellValue("B" + m, data.Data.QuotationList[i].Description);
                                    doc.SetCellValue("F" + m, data.Data.QuotationList[i].SORItem);
                                    if (data.Data.QuotationList[i].Qty > 0)
                                        doc.SetCellValue("H" + m, data.Data.QuotationList[i].Qty);

                                    doc.SetCellValue("I" + m, data.Data.QuotationList[i].Unit);
                                    if (data.Data.QuotationList[i].Rate > 0)
                                        doc.SetCellValue("J" + m, data.Data.QuotationList[i].Rate);

                                    if (data.Data.QuotationList[i].Amount > 0)
                                        doc.SetCellValue("K" + m, data.Data.QuotationList[i].Amount);

                                    TotalAmount += data.Data.QuotationList[i].Amount;
                                    tempIndex = m;
                                }
                                #endregion
                            }
                            else if (data.Data.IsJLLPOQuotation == true || data.Data.IsJLLPOInvoce == true)
                            {
                                #region JLLPO Items
                                for (int i = SIndex, m = tNo; i < EIndex && m < tempEnd; i++, m++)
                                {
                                    tempStrn = (data.Data.QuotationList[i].ItemNo == 0) ? "" : data.Data.QuotationList[i].ItemNo.ToString("00") + ".";
                                    doc.SetCellValue("A" + m, tempStrn);
                                    doc.SetCellValue("B" + m, data.Data.QuotationList[i].Description);
                                    doc.SetCellValue("F" + m, data.Data.QuotationList[i].SORItem);
                                    if (data.Data.QuotationList[i].Qty > 0)
                                        doc.SetCellValue("H" + m, data.Data.QuotationList[i].Qty);

                                    doc.SetCellValue("I" + m, data.Data.QuotationList[i].Unit);
                                    if (data.Data.QuotationList[i].Rate > 0)
                                        doc.SetCellValue("J" + m, data.Data.QuotationList[i].Rate);

                                    if (data.Data.QuotationList[i].Amount > 0)
                                        doc.SetCellValue("K" + m, data.Data.QuotationList[i].Amount);

                                    TotalAmount += data.Data.QuotationList[i].Amount;
                                    tempIndex = m;
                                }
                                #endregion
                            }

                            BroughtForward = CarryForward = TotalAmount;
                            if (CurrentPage != NoOfPages)
                            {
                                tempIndex += 1;
                                doc.MergeWorksheetCells("I" + tempIndex, "J" + tempIndex);
                                doc.SetCellValue("I" + tempIndex, "Carry Forward:");
                                style = doc.GetCellStyle("K16");
                                style.FormatCode = "#,##0.00";
                                style.Border.BottomBorder.BorderStyle = BorderStyleValues.Thin;
                                style.Border.BottomBorder.Color = System.Drawing.Color.Black;
                                style.Border.TopBorder.BorderStyle = BorderStyleValues.Thin;
                                style.Border.TopBorder.Color = System.Drawing.Color.Black;
                                doc.SetCellStyle("K" + tempIndex, style);
                                doc.SetCellValue("K" + tempIndex, CarryForward);
                            }
                            #endregion

                            //if (data.Data.IsJLLOrderQuotation == true || data.Data.IsJLLOrderInvoice == true)
                            //{
                            // (c) Update Work Completion Date
                            tempStrn = "Work completion on " + data.Data.WorkCompletionDate.ToString("dd MMMM yyyy", CultureInfo.CreateSpecificCulture("en-US"));
                            doc.SetCellValue("A" + WorkCompletionDateColumnNo, tempStrn);
                            //}
                            #endregion

                            #region Invoice Only - Total Amount
                            if (data.Data.IsQuotation == false && data.Data.IsInvoice == true)
                            {
                                String tempString = "** Say Hong Kong Dollars " + NumberUtil.changeCurrencyToWords((double)TotalAmount) + " **";
                                doc.SetCellValue("D" + InvoiceWordColumnNo, tempString);
                            }
                            #endregion

                            int tempTotalAmtColumnNo = TotalAmountColumnNo + NoOfRowsToBeAdded;
                            style = doc.GetCellStyle("K16");
                            style.FormatCode = "#,##0.00";
                            doc.SetCellStyle("K" + tempTotalAmtColumnNo, style);
                            doc.SetCellValue("K" + tempTotalAmtColumnNo, TotalAmount);

                            doc.InsertPageBreak(EndOfPageRow, EndOfPageColumn);

                            // You don't have to set this part.
                            // But if you don't, you have to manually change to
                            // PageBreakPreview mode in Excel to see the page breaks.
                            //SLPageSettings ps = doc.GetPageSettings();
                            //ps.View = SheetViewValues.PageBreakPreview;
                            //doc.SetPageSettings(ps);
                        }
                        doc.Save();
                    }
                }
                else
                    return false;

                IsSuccess = true;
            }
            catch (Exception e)
            {
                IsSuccess = false;
            }
            return IsSuccess;
        }

        private static int GetStartIndex(ExcelMain data, int currentPageNo)
        {
            return currentPageNo * data.MaxNoOfRecords;
        }

        private static int GetEndIndex(ExcelMain data, int currentPageNo, int NoOfPages)
        {
            int ResultIndex = (currentPageNo * data.MaxNoOfRecords);

            if (currentPageNo == NoOfPages)
            {
                if (data.Data.IsJLLOrderQuotation == true || data.Data.IsJLLOrderInvoice == true)
                    ResultIndex = data.Data.QuotationList.Count;
                else if (data.Data.IsJLLPOQuotation == true || data.Data.IsJLLPOInvoce == true)
                    ResultIndex = data.Data.QuotationList.Count;
            }

            return ResultIndex;
        }
    }
}